import MySQL


def normalize_proportion():
    # sql = 'SELECT `date`, SUM(count) AS sum FROM project_count WHERE `group`<>\'e\' GROUP BY `date`'
    sql = 'SELECT `date`, SUM(count) AS sum FROM project_count GROUP BY `date`'
    totals = MySQL.query(sql)
    sql = 'SELECT * FROM project_count'
    rows = MySQL.query(sql)
    for row in rows:
        for total in totals:
            if total['date'] == row['date']:
                row['proportion'] = row['count'] * 1.0 / total['sum']
                MySQL.update('project_count', {
                    'proportion': row['proportion']
                }, 'id=' + str(row['id']))
                break


def normalize_hour():
    sql = 'SELECT `date`, `hour`, SUM(count) AS sum FROM hour_count GROUP BY `date`, `hour`'
    totals = MySQL.query(sql)
    sql = 'SELECT * FROM hour_count'
    rows = MySQL.query(sql)
    for row in rows:
        for total in totals:
            if total['date'] == row['date'] and total['hour'] == row['hour']:
                row['proportion'] = row['count'] * 1.0 / total['sum']
                MySQL.update('hour_count', {
                    'proportion': row['proportion']
                }, 'id=' + str(row['id']))
                break


def normalize_hour_sum(date='20160101'):
    sql = 'SELECT * FROM hour_count WHERE `date`<=\'' + date + '\''
    rows = MySQL.query(sql)
    for row in rows:
        if row['date'] != date:
            continue
        sum_count = row['count']
        for pre in rows:
            if (pre['date'] == row['date'] and pre['group'] == row['group'] and pre['hour'] == row['hour'] - 1) \
                    or (pre['group'] == row['group'] and int(pre['date']) == int(row['date']) -1 and row['hour'] + 23 == pre['hour']):
                sum_count += pre['sum_count']
        row['sum_count'] = sum_count
        MySQL.update('hour_count', {
            'sum_count': sum_count
        }, 'id=' + str(row['id']))

    sql = 'SELECT `date`, `hour`, SUM(sum_count) AS sum FROM hour_count WHERE `date`=\'' + date + '\' GROUP BY `date`, `hour`'
    totals = MySQL.query(sql)
    sql = 'SELECT * FROM hour_count'
    rows = MySQL.query(sql)
    for row in rows:
        for total in totals:
            if total['date'] == row['date'] and total['hour'] == row['hour']:
                row['sum_proportion'] = row['sum_count'] * 1.0 / total['sum']
                MySQL.update('hour_count', {
                    'sum_proportion': row['sum_proportion']
                }, 'id=' + str(row['id']))
                break


if __name__ == '__main__':
    MySQL.init('wiki')
    # normalize_proportion()
    # normalize_hour()
    normalize_hour_sum('20160102')